﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading;
using System.Threading.Tasks;
using Ymatou.CommonService;

namespace YmtAuth.LogService
{
    public class SqlHelp
    {
        private static readonly string LoginInsertSQLCommand = "insert into {0}([RequestId] ,[UserId] ,[LoginId] ,[LoginIp] ,[ClientType] ,[TerminalType] ,[DeviceId] ,[ClientId] ,[AddTime] ,[RunTime] ,[AuthType] ,[ServerIp] ,[ResponseCode] ,[Desc]) values (@RequestId ,@UserId ,@LoginId ,@LoginIp ,@ClientType ,@TerminalType ,@DeviceId ,@ClientId ,@AddTime ,@RunTime ,@AuthType ,@ServerIp ,@ResponseCode ,@Desc)";

        private static readonly string authSafeVerifyInsertSQLCommand = "insert into authsafeverifylog(ApiId, RequestId, StrategyType, AppSource, UserId, LoginId, LoginIp, ServerIp, DeviceId, ClientId, AuthType, RunTime, ResponseCode, ResponseDesc, IpArea, LastAuthTime, ExpiredTime, CreateTime) values (@apiid, @requestid, @strategytype, @appsource, @userid, @loginid, @loginip, @serverip, @deviceid, @clientid, @authtype, @runtime, @responsecode, @responsedesc, @iparea, @lastauthtime, @expiredtime, @createtime)";
        private static readonly string PartyLoginInsertSQLCommand = "insert into LoginHistory_ThirdParty(_id,sLoginId,iAction,sLoginIp,sSummary,dAddTime,sClientType,RunTime,CSource,TSource,sDesc,ServerIp) values (@_id,@sLoginId,@iAction,@sLoginIp,@sSummary,@dAddTime,@sClientType,@RunTime,@CSource,@TSource,@Desc,@ServerIp)";
        private static readonly string tokenInsertSQL = "insert into Ymt_TokenHistory(_id,sLoginId,iAction,sLoginIp,sSummary,dAddTime,sClientType,RunTime,CSource,TSource,sDesc,ServerIp,DeviceId,ClientId) values (@_id,@sLoginId,@iAction,@sLoginIp,@sSummary,@dAddTime,@sClientType,@RunTime,@CSource,@TSource,@Desc,@ServerIp,@DeviceId,@ClientId)";
        private static readonly string userRegisterSQLCommand = "insert into Ymt_UserRegHistory(_id,LoginId,UserId,Mobile,LoginMail,ClientIp,CreateTime,Description,RegSource,RegResult,ChannelSource,TerminalSource, DeviceId, Equimentid) values (@_id,@LoginId,@UserId,@Mobile,@LoginMail,@ClientIp,@CreateTime,@Description,@RegSource,@RegResult,@ChannelSource,@TerminalSource,@DeviceId,@Equimentid)";
        private static readonly string blackIpSQLCommand = "insert into UserLockedLog(LoingId,UserId,LoginIp,OperateTime,Operator,[Description],OpType,AppSource) values (@LoingId,@UserId,@LoginIp,@OperateTime,@Operator,@Description,@OpType,@AppSource)";
        private static readonly string updateUserIpAreaAuthTimeSQLCommand = "update `useripauth` set `AuthTime`=sysdate(),`LastUpdateTime`=sysdate(),`Ip`=@Ip where `UserId`=@UserId AND `Country`=@Country AND `Province`=@Province AND `City`=@City limit 1;";
        /// <summary>
        /// 插入登录历史表
        /// </summary>
        /// <param name="userLogin"></param>
        /// <param name="timeOut"></param>
        /// <param name="connectionStringKey"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public static async Task InsertLoginLog(IEnumerable<UserLoginLogHistory> userLogin, TimeSpan timeOut, string connectionStringKey, string tableName)
        {
            var connString = ConfigurationManager.ConnectionStrings[connectionStringKey].ConnectionString;
            var tmpSQLCommand = string.Empty;
            using (var connection = new SqlConnection(connString))
            {
                try
                {
                    var cancel_open = new CancellationTokenSource(timeOut);
                    var cancel_execute = new CancellationTokenSource(timeOut);
                    tmpSQLCommand = string.Format(LoginInsertSQLCommand, tableName);
                    var command = new SqlCommand(tmpSQLCommand, connection);
                    await connection.OpenAsync(cancel_open.Token);
                    foreach (var log in userLogin)
                    {
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@RequestId", log.RequestId);
                        command.Parameters.AddWithValue("@UserId", log.UserId);
                        command.Parameters.AddWithValue("@LoginId", log.LoginId);
                        command.Parameters.AddWithValue("@LoginIp", log.LoginIp);
                        command.Parameters.AddWithValue("@ClientType", log.ClientType);
                        command.Parameters.AddWithValue("@TerminalType", log.TerminalType);
                        command.Parameters.AddWithValue("@DeviceId", log.DeviceId);
                        command.Parameters.AddWithValue("@ClientId", log.ClientId);
                        command.Parameters.AddWithValue("@AddTime", log.AddTime);
                        command.Parameters.AddWithValue("@RunTime", log.RunTime);
                        command.Parameters.AddWithValue("@AuthType", log.AuthType);
                        command.Parameters.AddWithValue("@ServerIp", log.ServerIp);
                        command.Parameters.AddWithValue("@ResponseCode", log.ResponseCode);
                        command.Parameters.AddWithValue("@Desc", log.Desc);
                        CheckParametersValue(command.Parameters);
                        await command.ExecuteNonQueryAsync(cancel_execute.Token).ConfigureAwait(false);
                    }
                }
                catch (AggregateException ex)
                {
                    ApplicationLog.Error("InsertLoginLog AggregateException {0}", ex);
                }
                catch (OperationCanceledException ex)
                {
                    ApplicationLog.Error("InsertLoginLog OperationCanceledException {0}", ex);
                }
                catch (Exception ex)
                {
                    ApplicationLog.Error(string.Format("InsertLoginLog Exception,sql: {0},error:{1}", tmpSQLCommand, ex));
                }
                finally
                {
                    connection.Close();
                }
            }

        }

        public static async Task InsertSafeVerifyLog(IEnumerable<SafeVerifyLog> verifyLog, TimeSpan timeOut, string connectionStringKey, string tableName)
        {
            using (var connection  = new MySqlConnection(ConfigurationManager.AppSettings[connectionStringKey]))
            {
                try
                {
                    var cancel_open = new CancellationTokenSource(timeOut);
                    var cancel_execute = new CancellationTokenSource(timeOut);
                    var command = new MySqlCommand(authSafeVerifyInsertSQLCommand, connection);
                    await connection.OpenAsync(cancel_open.Token);
                    foreach (var log in verifyLog)
                    {
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@apiid", log.ApiId);
                        command.Parameters.AddWithValue("@requestid", log.RequestId);
                        command.Parameters.AddWithValue("@strategytype", log.StrategyType);
                        command.Parameters.AddWithValue("@appsource", log.AppSource);

                        command.Parameters.AddWithValue("@userid", log.UserId);
                        command.Parameters.AddWithValue("@loginid", log.LoginId);
                        command.Parameters.AddWithValue("@loginip", log.LoginIp);
                        command.Parameters.AddWithValue("@serverip", log.ServerIp);

                        command.Parameters.AddWithValue("@deviceid", log.DeviceId);
                        command.Parameters.AddWithValue("@clientid", log.ClientId);
                        command.Parameters.AddWithValue("@authtype", log.AuthType);
                        command.Parameters.AddWithValue("@runtime", log.RunTime);

                        command.Parameters.AddWithValue("@responsecode", log.ResponseCode);
                        command.Parameters.AddWithValue("@responsedesc", log.ResponseDesc)                            ;
                        command.Parameters.AddWithValue("@iparea", log.IpArea);
                        command.Parameters.AddWithValue("@lastauthtime", log.LastAuthTime);

                        command.Parameters.AddWithValue("@expiredtime", log.ExpiredTime);
                        command.Parameters.AddWithValue("@createtime", log.CreateTime);

                        CheckParametersValue(command.Parameters);
                        await command.ExecuteNonQueryAsync(cancel_execute.Token).ConfigureAwait(false);
                    }
                }
                catch (AggregateException ex)
                {
                    ApplicationLog.Error("InsertSafeVerifyLog AggregateException {0}", ex);
                }
                catch (OperationCanceledException ex)
                {
                    ApplicationLog.Error("InsertSafeVerifyLog OperationCanceledException {0}", ex);
                }
                catch (Exception ex)
                {
                    ApplicationLog.Error(string.Format("InsertSafeVerifyLog Exception,sql: {0},error:{1}", authSafeVerifyInsertSQLCommand, ex.Message), ex);
                }
                finally
                {
                    connection.Close();
                }
            }

        }

        public static async Task InsertAuthSafeVerifyLog(IEnumerable<AuthSafeVerifyLog> verifyLog, TimeSpan timeOut, string connectionStringKey, string tableName)
        {
            var connString = ConfigurationManager.ConnectionStrings[connectionStringKey].ConnectionString;
            var tmpSQLCommand = string.Empty;
            using (var connection = new SqlConnection(connString))
            {
                try
                {
                    var cancel_open = new CancellationTokenSource(timeOut);
                    var cancel_execute = new CancellationTokenSource(timeOut);
                    tmpSQLCommand = string.Format(authSafeVerifyInsertSQLCommand, tableName);
                    var command = new SqlCommand(tmpSQLCommand, connection);
                    await connection.OpenAsync(cancel_open.Token);
                    foreach (var log in verifyLog)
                    {
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@_id", log._id);
                        command.Parameters.AddWithValue("@sLoginId", log.sLoginId);
                        command.Parameters.AddWithValue("@iAction", log.iAction);
                        command.Parameters.AddWithValue("@sLoginIp", log.sLoginIp);
                        command.Parameters.AddWithValue("@sSummary", log.sSummary);
                        command.Parameters.AddWithValue("@dAddTime", log.dAddTime);
                        command.Parameters.AddWithValue("@sClientType", log.sClientType);
                        command.Parameters.AddWithValue("@RunTime", log.RunTime);
                        command.Parameters.AddWithValue("@CSource", log.CSource);
                        command.Parameters.AddWithValue("@TSource", log.TSource);
                        command.Parameters.AddWithValue("@Desc", log.Desc);
                        command.Parameters.AddWithValue("@AuthType", log.AuthType);
                        command.Parameters.AddWithValue("@ResponseCode", log.ResponseCode);
                        command.Parameters.AddWithValue("@ServerIp", log.ServerIp);
                        command.Parameters.AddWithValue("@DeviceId", log.DeviceId);
                        command.Parameters.AddWithValue("@ClientId", log.ClientId);
                        CheckParametersValue(command.Parameters);
                        await command.ExecuteNonQueryAsync(cancel_execute.Token).ConfigureAwait(false);
                    }
                }
                catch (AggregateException ex)
                {
                    ApplicationLog.Error("InsertAuthLog AggregateException {0}", ex);
                }
                catch (OperationCanceledException ex)
                {
                    ApplicationLog.Error("InsertAuthLog OperationCanceledException {0}", ex);
                }
                catch (Exception ex)
                {
                    ApplicationLog.Error(string.Format("InsertAuthLog Exception,sql: {0},error:{1}", tmpSQLCommand, ex));
                }
                finally
                {
                    connection.Close();
                }
            }

        }
        public static async Task InsertTokenAuthLog(IEnumerable<TokenAuthLog> tokenArray, TimeSpan timeOut, string connectionStringKey)
        {
            var connString = ConfigurationManager.ConnectionStrings[connectionStringKey].ConnectionString;
            var tmpSQLCommand = string.Empty;
            using (var connection = new SqlConnection(connString))
            {
                try
                {
                    var cancel_open = new CancellationTokenSource(timeOut);
                    var cancel_execute = new CancellationTokenSource(timeOut);                  
                    var command = new SqlCommand(tokenInsertSQL, connection);
                    await connection.OpenAsync(cancel_open.Token);
                    foreach (var log in tokenArray)
                    {
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@_id", log._id);
                        command.Parameters.AddWithValue("@sLoginId", log.sLoginId);
                        command.Parameters.AddWithValue("@iAction", log.iAction);
                        command.Parameters.AddWithValue("@sLoginIp", log.sLoginIp);
                        command.Parameters.AddWithValue("@sSummary", log.sSummary);
                        command.Parameters.AddWithValue("@dAddTime", log.dAddTime);
                        command.Parameters.AddWithValue("@sClientType", log.sClientType);
                        command.Parameters.AddWithValue("@RunTime", log.RunTime);
                        command.Parameters.AddWithValue("@CSource", log.CSource);
                        command.Parameters.AddWithValue("@TSource", log.TSource);
                        command.Parameters.AddWithValue("@Desc", log.Desc);                      
                        command.Parameters.AddWithValue("@ServerIp", log.ServerIp);
                        command.Parameters.AddWithValue("@DeviceId", log.DeviceId);
                        command.Parameters.AddWithValue("@ClientId", log.ClientId); 
                        CheckParametersValue(command.Parameters);
                        await command.ExecuteNonQueryAsync(cancel_execute.Token).ConfigureAwait(false);
                    }
                }
                catch (AggregateException ex)
                {
                    ApplicationLog.Error("Insert token Log AggregateException {0}", ex);
                }
                catch (OperationCanceledException ex)
                {
                    ApplicationLog.Error("Insert token Log OperationCanceledException {0}", ex);
                }
                catch (Exception ex)
                {
                    ApplicationLog.Error(string.Format("Insert token Log Exception,sql: {0},error:{1}", tmpSQLCommand, ex));
                }
                finally
                {
                    connection.Close();
                }
            }

        }

        public static async Task UpdateUserIpAreaAuthTimeLog(IEnumerable<UpdateUserIpAreaTimeLog> logs, TimeSpan timeOut,
            string connectionStringKey)
        {
            using (var connection = new MySqlConnection(ConfigurationManager.ConnectionStrings[connectionStringKey].ConnectionString))
            {
                  try
                    {
                        var cancel_open = new CancellationTokenSource(timeOut);
                        var cancel_execute = new CancellationTokenSource(timeOut);
                        var command = new MySqlCommand(updateUserIpAreaAuthTimeSQLCommand, connection);
                        await connection.OpenAsync(cancel_open.Token);
                        foreach (var log in logs)
                        {
                            command.Parameters.Clear();
                            command.Parameters.Add("@UserId", MySqlDbType.Int32).Value = log.UserId;
                            command.Parameters.Add("@Country", MySqlDbType.VarString).Value = log.Country;
                            command.Parameters.Add("@Province", MySqlDbType.VarString).Value = log.Province;
                            command.Parameters.Add("@City", MySqlDbType.VarString).Value = log.City;
                            command.Parameters.Add("@Ip", MySqlDbType.VarString).Value = log.Ip;
                            //command.Parameters.Add("@AppSource", MySqlDbType.VarString).Value = log.AppSource;
                            CheckParametersValue(command.Parameters);
                            await command.ExecuteNonQueryAsync(cancel_execute.Token).ConfigureAwait(false);
                        }
                  }
                  catch (AggregateException ex)
                  {
                      ApplicationLog.Error("UpdateUserIpAreaAuthTimeLog Log AggregateException {0}", ex);
                  }
                  catch (OperationCanceledException ex)
                  {
                      ApplicationLog.Error("UpdateUserIpAreaAuthTimeLog Log OperationCanceledException {0}", ex);
                  }
                  catch (Exception ex)
                  {
                      ApplicationLog.Error(string.Format("UpdateUserIpAreaAuthTimeLog Log Exception,sql: {0},error:{1}", updateUserIpAreaAuthTimeSQLCommand, ex));
                  }
                  finally
                  {
                      connection.Close();
                  }
            }
        }

        public static async Task InsertThirdPartyLoginLog(IEnumerable<ThirdPartyUserLoginLog> tokenArray, TimeSpan timeOut, string connectionStringKey)
        {
            var connString = ConfigurationManager.ConnectionStrings[connectionStringKey].ConnectionString;
            var tmpSQLCommand = string.Empty;
            using (var connection = new SqlConnection(connString))
            {
                try
                {
                    var cancel_open = new CancellationTokenSource(timeOut);
                    var cancel_execute = new CancellationTokenSource(timeOut);
                    var command = new SqlCommand(PartyLoginInsertSQLCommand, connection);
                    await connection.OpenAsync(cancel_open.Token);
                    foreach (var log in tokenArray)
                    {
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@_id", log._id);
                        command.Parameters.AddWithValue("@sLoginId", log.sLoginId);
                        command.Parameters.AddWithValue("@iAction", log.iAction);
                        command.Parameters.AddWithValue("@sLoginIp", log.sLoginIp);
                        command.Parameters.AddWithValue("@sSummary", log.sSummary);
                        command.Parameters.AddWithValue("@dAddTime", log.dAddTime);
                        command.Parameters.AddWithValue("@sClientType", log.sClientType);
                        command.Parameters.AddWithValue("@RunTime", log.RunTime);
                        command.Parameters.AddWithValue("@CSource", log.CSource);
                        command.Parameters.AddWithValue("@TSource", log.TSource);
                        command.Parameters.AddWithValue("@Desc", log.Desc);
                        command.Parameters.AddWithValue("@ServerIp", log.ServerIp);                      
                        CheckParametersValue(command.Parameters);
                        await command.ExecuteNonQueryAsync(cancel_execute.Token).ConfigureAwait(false);
                    }
                }
                catch (AggregateException ex)
                {
                    ApplicationLog.Error("Insert token Log AggregateException {0}", ex);
                }
                catch (OperationCanceledException ex)
                {
                    ApplicationLog.Error("Insert token Log OperationCanceledException {0}", ex);
                }
                catch (Exception ex)
                {
                    ApplicationLog.Error(string.Format("Insert token Log Exception,sql: {0},error:{1}", tmpSQLCommand, ex));
                }
                finally
                {
                    connection.Close();
                }
            }

        }
        public static async Task UserLockedLog(IEnumerable<UserLockLog> blackListIp, TimeSpan timeOut, string connectionStringKey)
        {
            var connString = ConfigurationManager.ConnectionStrings[connectionStringKey].ConnectionString;
            using (var connection = new SqlConnection(connString))
            {
                var cancel_open = new CancellationTokenSource(timeOut);
                var cancel_execute = new CancellationTokenSource(timeOut);
                var command = new SqlCommand(blackIpSQLCommand, connection);
                await connection.OpenAsync(cancel_open.Token);
                try
                {
                    foreach (var blacklistInfo in blackListIp)
                    {
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@LoingId", blacklistInfo.LoginId);
                        command.Parameters.AddWithValue("@UserId", blacklistInfo.UserId);
                        command.Parameters.AddWithValue("@LoginIp", blacklistInfo.LoginIp);
                        command.Parameters.AddWithValue("@OperateTime", blacklistInfo.LoginTime);
                        command.Parameters.AddWithValue("@Operator", blacklistInfo.Operator);
                        command.Parameters.AddWithValue("@Description", blacklistInfo.Description);
                        command.Parameters.AddWithValue("@OpType", blacklistInfo.OpType);
                        command.Parameters.AddWithValue("@AppSource", blacklistInfo.AppSource);

                        CheckParametersValue(command.Parameters);
                        await command.ExecuteNonQueryAsync(cancel_execute.Token).ConfigureAwait(false);
                    }
                }
                catch (AggregateException ex)
                {
                    ApplicationLog.Error("UserLockedLog AggregateException {0}", ex);
                }
                catch (OperationCanceledException ex)
                {
                    ApplicationLog.Error("UserLockedLog OperationCanceledException {0}", ex);
                }
                catch (Exception ex)
                {
                    ApplicationLog.Error("UserLockedLog Exception {0}", ex);
                }
                finally
                {
                    connection.Close();
                }
            }
        }
        public static async Task InsertUserRegisterLog(IEnumerable<UserRegisterHistory> tokenArray, TimeSpan timeOut, string connectionStringKey)
        {
            var connString = ConfigurationManager.ConnectionStrings[connectionStringKey].ConnectionString;
            using (var connection = new SqlConnection(connString))
            {
                try
                {
                    var cancel_open = new CancellationTokenSource(timeOut);
                    var cancel_execute = new CancellationTokenSource(timeOut);
                    var command = new SqlCommand(userRegisterSQLCommand, connection);
                    await connection.OpenAsync(cancel_open.Token);
                    foreach (var user in tokenArray)
                    {
                        //@_id,@LoginId,@UserId,@Mobile,@LoginMail,@ClientIp,@CreateTime,@Description,@RegSource,@RegResult,@ChannelSource,@TerminalSource
                        command.Parameters.Clear();
                        command.Parameters.AddWithValue("@_id", Guid.NewGuid().ToString("N"));
                        command.Parameters.AddWithValue("@LoginId", user.LoginId);
                        command.Parameters.AddWithValue("@UserId", user.UserId);
                        command.Parameters.AddWithValue("@Mobile", user.Mobile);
                        command.Parameters.AddWithValue("@LoginMail", user.LoginMail);
                        command.Parameters.AddWithValue("@ClientIp", user.ClientIp);
                        command.Parameters.AddWithValue("@CreateTime", user.CreateTime);
                        command.Parameters.AddWithValue("@Description", user.Description);
                        command.Parameters.AddWithValue("@RegSource", user.RegSource);
                        command.Parameters.AddWithValue("@RegResult", user.RegResult);
                        command.Parameters.AddWithValue("@ChannelSource", user.ChannelSource);
                        command.Parameters.AddWithValue("@TerminalSource", user.TerminalSource);
                        command.Parameters.AddWithValue("@DeviceId", user.DeviceId);
                        command.Parameters.AddWithValue("@Equimentid", user.Equimentid);
                        CheckParametersValue(command.Parameters);
                        await command.ExecuteNonQueryAsync(cancel_execute.Token).ConfigureAwait(false);
                    }
                }
                catch (AggregateException ex)
                {
                    ApplicationLog.Error("InsertUserRegisterLog AggregateException {0}", ex);
                }
                catch (OperationCanceledException ex)
                {
                    ApplicationLog.Error("InsertUserRegisterLog OperationCanceledException {0}", ex);
                }
                catch (Exception ex)
                {
                    ApplicationLog.Error("InsertUserRegisterLog Exception {0}", ex);
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        public static void CheckParametersValue(SqlParameterCollection parameters)
        {
            foreach (IDataParameter param in parameters)
            {
                if (param.Value == null) param.Value = DBNull.Value;
            }
        }

        public static void CheckParametersValue(MySqlParameterCollection parameters)
        {
            foreach (IDataParameter param in parameters)
            {
                if (param.Value == null) param.Value = DBNull.Value;
            }
        }

       
    }
}
